package com.taekwon.www.remote.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.taekwon.www.remote.model.FxBoard;



public class DaoVer3 {
	public static void main(String[] args) {
		DaoVer3 dv3 = new DaoVer3();
		ArrayList<FxBoard> list = dv3.selectPaging(3, "");
		for(int i=0;i<list.size();i++){
			System.out.println(list.get(i).getFxseq());
		}
//		System.out.println(dv3.insert("babo", "babo", "babo"));
//		System.out.println(dv3.update("61","chunjae", "chunjae", "chunjae"));
		
	}
	public int getPageCount(String keyword){
		System.out.println("Dao[getPageCount]_start");
		int pageCount = 0;
		int rows = 0;
		ArrayList<FxBoard> list = new ArrayList<FxBoard>();		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = 	"select count(*) as fxcount  from FXBOARD "+
							"where FXCONTENT like '%"+keyword+"%' ";

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				rows= rs.getInt("fxcount");
			}
		}catch(Exception e){
			System.out.println("���ܹ߻� : " + e.toString());
		}finally{
			if(rs   != null) try{rs.close();  }catch(SQLException sqle){}
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}
		System.out.println("Dao[getPageCount]_end");
		pageCount = (rows-1)/10;
		if(pageCount==-1){pageCount=0;}
		return pageCount;
	}	
	
	public ArrayList<FxBoard> selectPaging(int page,String keyword){
		System.out.println("Dao[select]_start");
		ArrayList<FxBoard> list = new ArrayList<FxBoard>();		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = 	"select * from ( "+
							"select a.*, rownum rnum from ( "+
							"select FXSEQ, FXTITLE, FXCONTENT, FXHIT, FXWRITER, FXDATE from FXBOARD "+
							"where FXCONTENT like '%"+keyword+"%' "+
							"order by FXSEQ desc "+
							") a where rownum <= "+(page*10+10)+" "+
							") where rnum > "+(page*10);

			rs = stmt.executeQuery(sql);
			FxBoard fxBoard;
			while (rs.next()) {
				fxBoard = new FxBoard();
				fxBoard.setFxseq(rs.getInt("FXSEQ"));
				fxBoard.setFxtitle(rs.getString("FXTITLE"));
				fxBoard.setFxcontent(rs.getString("FXCONTENT"));
				fxBoard.setFxhit(rs.getInt("FXHIT"));
				fxBoard.setFxwriter(rs.getString("FXWRITER"));
				fxBoard.setFxdate(rs.getString("FXDATE"));
				list.add(fxBoard);				
			}
		}catch(Exception e){
			System.out.println("���ܹ߻� : " + e.toString());
		}finally{
			if(rs   != null) try{rs.close();  }catch(SQLException sqle){}
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}
		System.out.println("Dao[select]_end");
		return list;
	}	
	public ArrayList<FxBoard> selectFx(){
		System.out.println("Dao[select]_start");
		ArrayList<FxBoard> list = new ArrayList<FxBoard>();		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = 	"select FXSEQ, FXTITLE, FXCONTENT, FXHIT, FXWRITER, FXDATE	"+
							"from FXBOARD order by FXSEQ desc ";

			rs = stmt.executeQuery(sql);
			FxBoard fxBoard;
			while (rs.next()) {
				fxBoard = new FxBoard();
				fxBoard.setFxseq(rs.getInt("FXSEQ"));
				fxBoard.setFxtitle(rs.getString("FXTITLE"));
				fxBoard.setFxcontent(rs.getString("FXCONTENT"));
				fxBoard.setFxhit(rs.getInt("FXHIT"));
				fxBoard.setFxwriter(rs.getString("FXWRITER"));
				fxBoard.setFxdate(rs.getString("FXDATE"));
				list.add(fxBoard);				
			}
		}catch(Exception e){
			System.out.println("���ܹ߻� : " + e.toString());
		}finally{
			if(rs   != null) try{rs.close();  }catch(SQLException sqle){}
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}
		System.out.println("Dao[select]_end");
		return list;
	}

	public int deleteFx(String fxseq){
		System.out.println("Dao[delete]_start");
		System.out.println("Dao[delete]fxseq:"+fxseq);
		int appliedNum = 0;
		
		Connection conn = null;
		Statement stmt = null;

		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = "delete from fxboard where FXSEQ = '"+fxseq+"'";
			System.out.println("Dao[delete]sql:"+sql);
			appliedNum = stmt.executeUpdate(sql);
			System.out.println("Dao[delete]appliedNum:"+appliedNum);
		}catch(Exception e){
			System.out.println("Dao[delete]error:" + e.toString());
		}finally{
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}	
		System.out.println("Dao[delete]_end");
		return appliedNum;
	}
	public int insertFx(String fxtitle,String fxcontent,String fxwriter){
		System.out.println("Dao[insert]_start");
		System.out.println("Dao[insert]fxtitle:"+fxtitle);
		System.out.println("Dao[insert]fxcontent:"+fxcontent);
		System.out.println("Dao[insert]fxwriter:"+fxwriter);
		int appliedNum = 0;
		
		Connection conn = null;
		Statement stmt = null;

		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = 	"INSERT INTO FXBOARD "+
							"(FXSEQ, FXTITLE, FXCONTENT, FXHIT, FXWRITER, FXDATE) "+
							"VALUES (fxseq.nextval, '"+fxtitle+"', '"+fxcontent+"', '1', '"+fxwriter+"', to_char(sysdate,'YYYYMMDD'))";

			System.out.println("Dao[insert]sql:"+sql);
			appliedNum = stmt.executeUpdate(sql);
			System.out.println("Dao[insert]appliedNum:"+appliedNum);
		}catch(Exception e){
			System.out.println("Dao[insert]error:" + e.toString());
		}finally{
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}	
		System.out.println("Dao[insert]_end");
		return appliedNum;
	}
	public int updateFx(String fxseq,String fxtitle,String fxcontent,String fxwriter){
		System.out.println("Dao[update]_start");
		System.out.println("Dao[update]fxseq:"+fxseq);
		System.out.println("Dao[update]fxtitle:"+fxtitle);
		System.out.println("Dao[update]fxcontent:"+fxcontent);
		System.out.println("Dao[update]fxwriter:"+fxwriter);
		int appliedNum = 0;
		
		Connection conn = null;
		Statement stmt = null;

		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(
					"jdbc:oracle:thin:@127.0.0.1:1521:xe", "sem", "java");
			stmt = conn.createStatement();
			String sql = 	"update FXBOARD "+
							"set "+
							"FXTITLE='"+fxtitle+"', "+
							"FXCONTENT='"+fxcontent+"', "+
							"FXWRITER='"+fxwriter+"', "+
							"FXDATE=to_char(sysdate,'YYYYMMDD') "+
							"where FXSEQ="+fxseq+" ";
			
			System.out.println("Dao[update]sql:"+sql);
			appliedNum = stmt.executeUpdate(sql);
			System.out.println("Dao[update]appliedNum:"+appliedNum);
		}catch(Exception e){
			System.out.println("Dao[update]error:" + e.toString());
		}finally{
			if(stmt != null) try{stmt.close();}catch(SQLException sqle){}
			if(conn != null) try{conn.close();}catch(SQLException sqle){}
		}	
		System.out.println("Dao[update]_end");
		return appliedNum;
	}	
}
